package jsyf.oa.dao.yuce;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.yuce.YuceMainEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;


/**
 * Created by y on 2018/3/19.
 */
@Repository
public class YuceMainDao extends BaseDao {

    public YuceMainDao() {
        super.iBaseEntity = new YuceMainEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, YuceMainEntity.class);
    }

    public JSONObject getTitle(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append("  t_sys_company.id as companyId,");
        sql.append(" t_sys_region. NAME AS regionName,");
        sql.append(" t_sys_company.`NAME` AS companyName");
        sql.append(" FROM t_sys_region");
        sql.append(" LEFT JOIN t_sys_company ON t_sys_region.COMPANY_ID = t_sys_company.id where 1=1");
        sql.append(condition);
        sql.append(" GROUP BY t_sys_company.id");
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getRegionTree(String userId, String companyIds) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t_sys_region.ID as regionId,t_sys_region.`NAME` as regionName,t_sys_region.COMPANY_ID as companyId,t_sys_region.SORT_NO as sortNo\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_region.id=t_sys_user_2_region.REGION_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_region.PARENT_ID=0\n" +
                "AND t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.company_id in (" + companyIds + ")\n" +
                "group by t_sys_region.ID \n" +
                "ORDER BY t_sys_region.SORT_NO");
        return super.queryForJsonList(sb.toString());
    }

    public Integer getYuceStatus(String regionId, String date) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t_rpt_friday_yuce_main.`STATUS` as `status`\n" +
                " FROM t_rpt_friday_yuce_main\n" +
                " WHERE 1=1\n" +
                " AND t_rpt_friday_yuce_main.BIG_REGION_ID='" + regionId + "'\n" +
                " AND `DAY`='" + date + "'\n");
        List<JSONObject> jsonObjectList = super.queryForJsonList(sb.toString());
        if (null != jsonObjectList && jsonObjectList.size() > 0) {
            return Integer.parseInt(jsonObjectList.get(0).getString("status"));
        } else {
            return -1;
        }
    }

    public Boolean getYuceStatus(String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t_sys_company_2_brand.COMPANY_ID\n" +
                "FROM t_sys_company_2_brand \n" +
                "LEFT JOIN t_dvc_brand ON t_sys_company_2_brand.BRAND_ID=t_dvc_brand.ID\n" +
                "WHERE 1=1\n" +
                "AND t_dvc_brand.DEL_FLAG=0\n" +
                "AND t_dvc_brand.BRAND_NAME='约翰迪尔'\n" +
                "AND t_sys_company_2_brand.COMPANY_ID='" + companyId + "'\n");
        List<JSONObject> jsonObjectList = super.queryForJsonList(sb.toString());
        if (null != jsonObjectList && jsonObjectList.size() > 0) {
            return true;
        } else {
            return false;
        }
    }

   /* public JSONObject getNumberSum(String mainIds) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT IFNULL(SUM(t_rpt_friday_yuce_main.ACTUAL_SALE_REMARK),0) as number\n" +
                "FROM t_rpt_friday_yuce_main\n" +
                "WHERE id in ('" + mainIds + "')\n");
        List<JSONObject> jsonObjectList = super.queryForJsonList(sb.toString());
        if (null != jsonObjectList && jsonObjectList.size() > 0) {
            return jsonObjectList.get(0);
        } else {
            return null;
        }
    }*/

    public JSONObject getBeforeWeekYuceSum(String mainIds) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tIFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.FIRST_MONTH_BEF\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS firstMonthBef\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.SECOND_MONTH_BEF\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS secondMonthBef\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.THIRD_MONTH_BEF\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS thirdMonthBef\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.FIRST_MONTH_SUB\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS firstMonthSub\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.SECOND_MONTH_SUB\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS secondMonthSub\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.THIRD_MONTH_SUB\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS thirdMonthSub\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.FIRST_MONTH_YUCE\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS firstMonthYuce\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.SECOND_MONTH_YUCE\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS secondMonthYuce\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.THIRD_MONTH_YUCE\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS thirdMonthYuce\n" +
                "\t,IFNULL(\n" +
                "\t\tSUM(\n" +
                "\t\t\tt_rpt_friday_yuce_main.ACTUAL_SALE_REMARK\n" +
                "\t\t),\n" +
                "\t\t0\n" +
                "\t) AS number\n" +
                "FROM\n" +
                "\tt_rpt_friday_yuce_main\n" +
                "WHERE\n" +
                "\tt_rpt_friday_yuce_main.ID IN('" + mainIds + "')");
        List<JSONObject> jsonObjectList = super.queryForJsonList(sb.toString());
        if (null != jsonObjectList && jsonObjectList.size() > 0) {
            return jsonObjectList.get(0);
        } else {
            return null;
        }
    }

    public  List<JSONObject> getFridayList(String condition,String firstMonth,String secondMonth,String thirdMonth,String oldWeekFriday,String wednesday){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tMODEL_NAME AS modelName,\n" +
                "\tIFNULL(t2.firstMonth ,0) AS firstMonthNum,\n" +
                "\tIFNULL(t2.secondMonth ,0) AS secondMonthNum,\n" +
                "\tIFNULL(t2.thirdMonth ,0) AS thirdMonthNum,\n" +
                "  IFNULL(t2.oldFirstMonth ,0) AS oldFirstMonthNum,\n" +
                "\tIFNULL(t2.oldSecondMonth ,0) AS oldSecondMonthNum,\n" +
                "\tIFNULL(t2.oldThirdMonth ,0) AS oldThirdMonthNum,\n" +
                "\tIFNULL(t2.firstFinishNumber,0) AS firstFinishNum,\n" +
                "\tIFNULL(t2.secondFinishNumber,0) AS secondFinishNum,\n" +
                "\tIFNULL(t2.thirdFinishNumber ,0) AS thirdFinishNum,\n" +
                "t2.needModelId AS needModelId,\n" +
                "t2.needModel AS needMOdelName,\n" +
                "t2.bigRegionId AS bigRegionId,\n" +
                "t2.companyId AS companyId,\n" +
                "'"+oldWeekFriday+"'  AS lastWeek\n"+
                "FROM\n" +
                "\tt_dvc_model\n" +
                "LEFT JOIN t_dvc_brand ON t_dvc_brand.ID = t_dvc_model.BRAND_ID\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt1.needModel,\n" +
                "\t\tsum(t1.firstMonth) AS firstMonth,\n" +
                "\t\tsum(t1.secondMonth) AS secondMonth,\n" +
                "\t\tsum(t1.thirdMonth) AS thirdMonth,\n" +
                "\t\tsum(t1.oldFirstMonth) AS oldFirstMonth,\n" +
                "\t\tsum(t1.oldSecondMonth) AS oldSecondMonth,\n" +
                "\t\tsum(t1.oldThirdMonth) AS oldThirdMonth,\n" +
                "\t\tsum(t1.firstFinishNumber) AS firstFinishNumber,\n" +
                "\t\tsum(t1.secondFinishNumber) AS secondFinishNumber,\n" +
                "\t\tsum(t1.thirdFinishNumber) AS thirdFinishNumber,\n" +
                "\t\tt1.needModelId,\n" +
                "\t\tt1.bigRegionId,\n" +
                "\t\tt1.companyId\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_rpt_thursday_genzong.NEED_MODEL AS needModel,\n" +
                "\t\t\t\t`DAY` AS `day`,\n" +
                "\t\t\t\tDELIVERY_DATE AS `deliveryDate`,\n" +
                "\t\t\t\tCONFIRMATION_STATUS AS `confirmationStatus`,\n" +
                "\n" +
                "\t\t\tCASE\n" +
                "\t\t\tWHEN CONFIRMATION_STATUS LIKE ('%丢单%')\n" +
                "\t\t\tOR CONFIRMATION_STATUS LIKE ('%已交车%') THEN\n" +
                "\t\t\t\t0\n" +
                "\t\t\tWHEN locate('"+firstMonth+"', `DELIVERY_DATE`) AND `DAY`='"+wednesday+"' THEN  \n" +
                "\t\t\t\t1\n" +
                "\t\t\tELSE\n" +
                "\t\t\t\t0\n" +
                "\t\t\tEND firstMonth,\n" +
                "\n" +
                "\n" +
                "\t\tCASE\n" +
                "\t\tWHEN CONFIRMATION_STATUS LIKE ('%丢单%')\n" +
                "\t\tOR CONFIRMATION_STATUS LIKE ('%已交车%') THEN\n" +
                "\t\t\t0\n" +
                "\t\tWHEN locate('"+secondMonth+"', `DELIVERY_DATE`) AND `DAY`='"+wednesday+"' THEN\n" +
                "\t\t\t1\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND secondMonth,\n" +
                "\n" +
                "\n" +
                "\tCASE\n" +
                "\tWHEN CONFIRMATION_STATUS LIKE ('%丢单%')\n" +
                "\tOR CONFIRMATION_STATUS LIKE ('%已交车%') THEN\n" +
                "\t\t0\n" +
                "\tWHEN locate('"+thirdMonth+"', `DELIVERY_DATE`) AND `DAY`='"+wednesday+"' THEN\n" +
                "\t\t1\n" +
                "\tELSE\n" +
                "\t\t0\n" +
                "\tEND thirdMonth,\n" +
                "\n" +
                "\n" +
                "\n" +
                "  CASE\n" +
                "\tWHEN CONFIRMATION_STATUS LIKE ('%丢单%')\n" +
                "\tOR CONFIRMATION_STATUS LIKE ('%已交车%') THEN\n" +
                "\t\t0\n" +
                "\tWHEN  locate('"+firstMonth+"', `DELIVERY_DATE`) AND `DAY`='"+oldWeekFriday+"' THEN\n" +
                "\t\t1\n" +
                "\tELSE\n" +
                "\t\t0\n" +
                "\tEND oldFirstMonth,\n" +
                "CASE\n" +
                "\tWHEN CONFIRMATION_STATUS LIKE ('%丢单%')\n" +
                "\tOR CONFIRMATION_STATUS LIKE ('%已交车%') THEN\n" +
                "\t\t0\n" +
                "\tWHEN locate('"+secondMonth+"', `DELIVERY_DATE`) AND `DAY`='"+oldWeekFriday+"' THEN\n" +
                "\t\t1\n" +
                "\tELSE\n" +
                "\t\t0\n" +
                "\tEND oldSecondMonth,\n" +
                "\n" +
                "CASE\n" +
                "\tWHEN CONFIRMATION_STATUS LIKE ('%丢单%')\n" +
                "\tOR CONFIRMATION_STATUS LIKE ('%已交车%') THEN\n" +
                "\t\t0\n" +
                "\tWHEN  locate('"+thirdMonth+"', `DELIVERY_DATE`) AND `DAY`='"+oldWeekFriday+"' THEN\n" +
                "\t\t1\n" +
                "\tELSE\n" +
                "\t\t0\n" +
                "\tEND oldThirdMonth,\n" +
                "\n" +
                "\n" +
                "CASE\n" +
                "WHEN CONFIRMATION_STATUS LIKE ('%已交车%')\n" +
                "AND locate('"+firstMonth+"', `DELIVERY_DATE`) AND `DAY`='"+wednesday+"' THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "\t0\n" +
                "END firstFinishNumber,\n" +
                "\n" +
                "CASE\n" +
                "WHEN CONFIRMATION_STATUS LIKE ('%已交车%')\n" +
                "AND locate('"+secondMonth+"', `DELIVERY_DATE`) AND `DAY`='"+wednesday+"'THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "\t0\n" +
                "END secondFinishNumber,\n" +
                "\tCASE\n" +
                "WHEN CONFIRMATION_STATUS LIKE ('%已交车%')\n" +
                "AND locate('"+thirdMonth+"', `DELIVERY_DATE`) AND `DAY`='"+wednesday+"' THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "\t0\n" +
                "END thirdFinishNumber,\n" +
                " NEED_MODEL_ID AS needModelId,\n" +
                " BIG_REGIONAL_ID AS bigRegionId,\n" +
                " COMPANY_ID AS companyId\n" +
                "FROM\n" +
                "\tt_rpt_thursday_genzong\n" +
               "WHERE 1=1\n"+condition+
                "\t\t) AS t1\n" +
                "\tGROUP BY\n" +
                "\t\tneedModelId\n" +
                ") AS t2 ON t2.needModelId = t_dvc_model.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_dvc_brand.BRAND_NAME = '约翰迪尔'\n" +
                "\n" +
                "ORDER BY\n" +
                "\tt_dvc_model.SORT_NO");
        return super.queryForJsonList(sql.toString());
    }
}
